package ALMySqlCommon.ALMySqlSafeOp;

import java.nio.ByteBuffer;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLNonTransientConnectionException;
import java.sql.SQLRecoverableException;
import java.sql.SQLSyntaxErrorException;
import java.sql.SQLTimeoutException;
import java.sql.SQLTransientConnectionException;
import java.sql.SQLWarning;
import java.util.ArrayList;
import java.util.Base64;

import com.mysql.cj.jdbc.exceptions.MySQLQueryInterruptedException;

import ALBasicCommon.ALBasicCommonFun;
import ALMySqlCommon.ALMySqlCommonObj.ALMySqlConnectionPool._AALMySqlBaseDBObj;
import ALServerLog.ALServerLog;

/**********************
 * 数据库安全操作类中单个数据库操作的数据对象
 * 
 * 从Scott项目代码优化迁移
 * @author mj
 *
 */
public class ALMySqlSafeOpData 
{
	//默认的空字节数组对象
    private static final byte[] _g_arrEmptyBinary = new byte[0];
    
	//执行脚本的序列号
    private long _m_lSerialize;
    //执行的具体Sql语句
    private String _m_SQuery;
    //执行语句可能的参数
    private ArrayList<byte[]> _m_lByteList;
    //添加的时间，只是用于显示
    private long _m_lAddTimeMS;

    public ALMySqlSafeOpData()
    {
    	_m_lSerialize = 0;
    	_m_SQuery = "";
    	_m_lByteList = null;
    	
    	_m_lAddTimeMS = 0;
    }
    public ALMySqlSafeOpData(long _serialize, String _query, long _addTimeMS)
    {
    	_m_lSerialize = _serialize;
    	_m_SQuery = _query;
    	_m_lByteList = null;
    	
    	_m_lAddTimeMS = _addTimeMS;
    }
    public ALMySqlSafeOpData(long _serialize, String _query, ArrayList<byte[]> _byteList, long _addTimeMS)
    {
    	_m_lSerialize = _serialize;
    	_m_SQuery = _query;
    	_m_lByteList = _byteList;
    	
    	_m_lAddTimeMS = _addTimeMS;
    }
    
    public long getSerialize() {return _m_lSerialize;}
    public String getQuery() {return _m_SQuery;}
    public ArrayList<byte[]> getByteList() {return _m_lByteList;}
    public long getAddTimeMS() {return _m_lAddTimeMS;}

    /********************
     * 在对应数据库执行对应语句，并返回执行结果
     * @param _dbObj
     * @return
     */
    public EALMySqlSafeOpErr execSQL(_AALMySqlBaseDBObj _dbObj)
    {
    	//带入对象错误，按照无连接处理
    	if(null == _dbObj)
    		return EALMySqlSafeOpErr.CONN_ERR;
    	
        Connection con = null;
        PreparedStatement stmt = null;
        try
        {
        	//申请数据库操作连接
            con = _dbObj.getConn();
            if (con == null)
                return EALMySqlSafeOpErr.CONN_ERR;
            
            //准备语句的执行
            stmt = con.prepareStatement(_m_SQuery);
            //根据是否有字节参数，将字节参数队列带入操作语句执行对象中
            if (null != _m_lByteList && !_m_lByteList.isEmpty())
            {
                for (int i = 0; i < _m_lByteList.size(); ++i)
                {
                    byte[] binaryData = _m_lByteList.get(i);
                    if (binaryData == null)
                    {
                        binaryData = _g_arrEmptyBinary;
                    }
                    
                    stmt.setBytes(i + 1, binaryData);
                }
            }
            
            //执行语句
            stmt.executeUpdate();
        }
        catch (SQLSyntaxErrorException e)
        {
        	//语法错误，不做特殊处理
            ALServerLog.Error("[MySqlSafeOp SQLSyntaxErrorException] Message: " + e.getMessage()
            		+ "\nDBName: " + _dbObj.getDBName() 
            		+ "\nSerialize: " + _m_lSerialize 
            		+ "\nQuery: " + _m_SQuery);
            
            return EALMySqlSafeOpErr.SYNTAX_ERR;
        }
        catch (SQLDataException e)
        {
            ALServerLog.Error("[MySqlSafeOp SQLDataException] Message: " + e.getMessage()
    		+ "\nDBName: " + _dbObj.getDBName() 
    		+ "\nSerialize: " + _m_lSerialize 
    		+ "\nQuery: " + _m_SQuery);

            return EALMySqlSafeOpErr.DATA_ERR;
        }
        catch (SQLWarning e)
        {
            ALServerLog.Error("[MySqlSafeOp SQLWarning] Message: " + e.getMessage()
    		+ "\nDBName: " + _dbObj.getDBName() 
    		+ "\nSerialize: " + _m_lSerialize 
    		+ "\nQuery: " + _m_SQuery);
            
            return EALMySqlSafeOpErr.DATA_ERR;
        }
        catch (SQLNonTransientConnectionException | SQLTimeoutException | SQLTransientConnectionException | SQLRecoverableException  | MySQLQueryInterruptedException e) 
        {
            ALServerLog.Error("[MySqlSafeOp " + e.getClass() + "] Message: " + e.getMessage()
    		+ "\nDBName: " + _dbObj.getDBName() 
    		+ "\nSerialize: " + _m_lSerialize 
    		+ "\nQuery: " + _m_SQuery);
            
            return EALMySqlSafeOpErr.CONN_ERR;
        } 
        catch (SQLException e)
        {
        	//部分数据库切换的时候会出报错，暂时没有很好的判断方式，这里通过文本直接判断
        	if(e.getMessage().equalsIgnoreCase("The MySQL server is running with the --read-only option so it cannot execute this statement"))
        	{
                ALServerLog.Error("[MySqlSafeOp " + e.getClass() + "] Message: " + e.getMessage()
        		+ "\nDBName: " + _dbObj.getDBName() 
        		+ "\nSerialize: " + _m_lSerialize 
        		+ "\nQuery: " + _m_SQuery);
                
                return EALMySqlSafeOpErr.CONN_ERR;
        	}
        	else
        	{
	            ALServerLog.Error("[MySqlSafeOp Throwable] Message: " + e.getMessage()
	    		+ "\nDBName: " + _dbObj.getDBName() 
	    		+ "\nSerialize: " + _m_lSerialize 
	    		+ "\nQuery: " + _m_SQuery);
	            
	            return EALMySqlSafeOpErr.OTHER_ERR;
        	}
        }
        catch (Throwable e) {
            ALServerLog.Error("[MySqlSafeOp Throwable] Message: " + e.getMessage()
    		+ "\nDBName: " + _dbObj.getDBName() 
    		+ "\nSerialize: " + _m_lSerialize 
    		+ "\nQuery: " + _m_SQuery);
            
            return EALMySqlSafeOpErr.OTHER_ERR;
        } finally {
            try {
                if(null != stmt)
                    stmt.close();
                stmt = null;
                
            } catch (Exception ignored){
            }
            try {
                if(null != con)
                    con.close();
                
                con = null;
            } catch (Exception ignored) {
            }
        }
        
        //返回无错误
        return EALMySqlSafeOpErr.NONE;
    }

    /***********************
     * 从字符串读取本结构体的数据
     * @param _rawQuery
     * @return
     */
    public boolean parseFromString(String _str)
    {
        String[] strs = ALBasicCommonFun.charSplit(_str, '\t');
        if(strs.length <4)
        {
            ALServerLog.Error("invalid query format,need at least 4 field: " + _str);
            return false;
        }
        
        _m_lSerialize = Long.parseLong(strs[0].trim());
        _m_lAddTimeMS = Long.parseLong(strs[1].trim());
        
        //第三个字段只是用于展示内容，方便查看，没有其他的意义。这里不做读取

        _m_SQuery = decodeString(strs[3]);
        if(strs.length >= 5)
        {
            _m_lByteList = decodeByteList(strs[4]);
        }
        
        return true;
    }

    /*************
     * 构造导出的字符串内容
     */
    @Override
    public String toString()
    {
        StringBuilder sb = new StringBuilder();
        
        sb.append(_m_lSerialize).append('\t');
        sb.append(_m_lAddTimeMS).append('\t');

        //将语句的前一段字符串裁剪出来，放入。方便后续查看和简单过滤
        String minQuery = _m_SQuery.substring(0, 50);
        minQuery = minQuery.replace("\t","    ");
        sb.append(minQuery).append('\t');

        sb.append(encodeString(_m_SQuery)).append('\t');
        sb.append(encodeByteList(_m_lByteList));
        return sb.toString();
    }

    /*********************
     * 将字符串解码成对应的字节数据
     * @param str
     * @return
     */
    private ArrayList<byte[]> decodeByteList(String _str)
    {
        if (_str == null || _str.isEmpty())
            return null;
        
        ArrayList<byte[]> list = new ArrayList<>();
        byte[] decode = Base64.getDecoder().decode(_str);
        
        ByteBuffer buffer = ByteBuffer.wrap(decode);
        short size = buffer.getShort();
        for(int i=0;i<size;i++)
        {
        	//获取长度
            int len = buffer.getInt();
            
            //构造数据并读取
            byte[] bytes = new byte[len];
            buffer.get(bytes);
            
            list.add(bytes);
        }
        
        return list;
    }

    /****************
     * 将字节内容输出为字符串用于保存
     * @param _byteList
     * @return
     */
    private String encodeByteList(ArrayList<byte[]> _byteList)
    {
        if (null == _byteList || _byteList.isEmpty())
            return "";

        //计算总长度
        int length = 2;
        for (byte[] bytes : _byteList)
        {
        	//每个数据使用4字节存放长度
            length += 4;
            
            if(null != bytes)
            	length += bytes.length;
        }
        
        ByteBuffer buffer = ByteBuffer.allocate(length);
        int size = _byteList.size();
        buffer.putShort((short) size);
        for (byte[] bytes : _byteList)
        {
            if(null == bytes)
            {
                buffer.putInt(0);
                continue;
            }
            
            int len = bytes.length;
            buffer.putInt(len);
            buffer.put(bytes);
        }
        byte[] bytes = ALBasicCommonFun.ByteBfferToBytes(buffer);
        
        //进行编码保证出来的字符串是符合日常规则的
        return  Base64.getEncoder().encodeToString(bytes);
    }

    /****************
     * 对字符串进行编码加密解密，确保写入读取不会因为特殊编码出现问题
     * @param str
     * @return
     */
    private String decodeString(String str)
    {
        byte[] decode = Base64.getDecoder().decode(str);
        return new String(decode,StandardCharsets.UTF_8);
    }
    private String encodeString(String str)
    {
        byte[] bytes = str.getBytes(StandardCharsets.UTF_8);
        return  Base64.getEncoder().encodeToString(bytes);
    }
}
